package de.tum.in.eist.poll.server;

import de.tum.in.eist.poll.shared.Lecture;

import java.io.Serializable;
import java.util.ArrayList;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpSession;

import java.sql.Connection;

import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

import com.google.gwt.user.server.rpc.RemoteServiceServlet;

import de.tum.in.eist.poll.client.PollService;
import de.tum.in.eist.poll.shared.*;

/**
 * Server-side interface implementation. Provides session and database functionality.
 * 
 */
public class PollServiceImpl extends RemoteServiceServlet implements
		PollService, Serializable {
	private static final long serialVersionUID = 1L;
	
	/**
	 * The connection string for the Apache Derby database which is accessed via jdbc.
	 */
	private static String connStr = "jdbc:derby:pollDB;create=true";

	public PollServiceImpl() {

	}

	/**
	 * Login method. Checks the user's login credentials and creates a session.
	 * 
	 * @param name The user's username
	 * @param password The user's password as entered during login, in unencrypted form
	 * @return a complete User object containing all the data stored in the database's users table
	 */
	@Override
	public User login(String name, String password) {
		User usr = null;

		Connection conn = null;
		PreparedStatement stmt = null;
		ResultSet rs = null;

		try {
			conn = DriverManager.getConnection(connStr);
			stmt = conn
					.prepareStatement("SELECT userid, pwhash, usertype, notification FROM users WHERE name=?");
			stmt.setString(1, name);

			rs = stmt.executeQuery();

			int usertype, userid;
			String passworddb;

			while (rs.next()) {
				userid = rs.getInt("userid");
				usertype = rs.getInt("usertype");
				rs.getInt("notification");
				passworddb = rs.getString("pwhash");

				// only create user object to return if password is correct
				if (BCrypt.checkpw(password, passworddb)) {
					usr = new User(name, passworddb, userid, usertype);
				}
			}
		} catch (SQLException sqle)
        {
            printSQLException(sqle);
        } finally {
        	closeDB(conn);
        	closeDB(stmt, rs);
        }

		if (usr == null) { // User does not exist or password was incorrect
			// TODO Add Exception things here
			return null;
		}

		usr.login();
		storeUserInSession(usr);

		return usr;
	}

	/**
	 * Auxiliary login method. Returns the user object stored in the session.
	 * 
	 * @return a complete User object containing all the data stored in the database's users table
	 */
	@Override
	public User loginFromSession() {
		User usr = getUserFromSession();
		return usr;
	}

	/**
	 * Terminates the users session by removing the identifying object.
	 * 
	 */
	@Override
	public void logout() {
		HttpServletRequest request = this.getThreadLocalRequest();
		HttpSession session = request.getSession();
		session.removeAttribute("usr");
	}

	/**
	 * Stores the user object in the session for later retrieval by both the client and the server.
	 * 
	 */
	private void storeUserInSession(User user) {
		HttpServletRequest request = this.getThreadLocalRequest();
		HttpSession session = request.getSession();
		session.setAttribute("usr", user);
	}

	/**
	 * Server-side auxiliary method. Fetches the user object of a user who is currently logged in, bypassing any database access.
	 * 
	 */
	private User getUserFromSession() {
		User usr = null;
		HttpServletRequest request = this.getThreadLocalRequest();
		HttpSession session = request.getSession();
		Object usrObject = session.getAttribute("usr");
		if (usrObject != null && usrObject instanceof User) {
			usr = (User) usrObject;
		}
		return usr;
	}

	/**
	 * saves submitted feedback to the database if the student did not already
	 * submit feedback for this lecture.
	 * 
	 * @param feedback
	 *            The feedback object containing the level of satisfaction and
	 *            the like and dislike strings
	 */
	public void submitFeedback(Feedback feedback) {
		User usr = getUserFromSession();
		int 	studentID = usr.getID(), 
				lectureID = feedback.getLectureID(), 
				satisfaction = feedback.getSatisfaction();
		
		String 	like = feedback.getLike(),
				dislike = feedback.getDislike();

		Connection conn = null;
		PreparedStatement stmt = null;
		ResultSet rs = null;
		
		try {
			conn = DriverManager.getConnection(connStr);

			// check if student has already submitted feedback for this lecture
			stmt = conn
					.prepareStatement("SELECT * FROM feedbackreceived WHERE lectureid=? AND studentid=?");
			stmt.setInt(1, lectureID);
			stmt.setInt(2, studentID);

			rs = stmt.executeQuery();

			if (!rs.next()) { // zero rows returned, no feedback by this student
								// for this lecture
				// add feedback to database
				stmt = conn
						.prepareStatement("INSERT INTO feedback (satisfaction, commentlike, commentdislike, lectureid) VALUES (?, ?, ?, ?)");
				stmt.setInt(1, satisfaction);
				stmt.setString(2, like);
				stmt.setString(3, dislike);
				stmt.setInt(4, lectureID);

				stmt.executeUpdate();
				// add student to list of those who have submitted feedback for
				// this lecture
				stmt = conn
						.prepareStatement("INSERT INTO feedbackreceived (lectureid, studentid) VALUES (?, ?)");
				stmt.setInt(1, lectureID);
				stmt.setInt(2, studentID);

				stmt.executeUpdate();
			}

		} catch (SQLException sqle)
        {
            printSQLException(sqle);
        } finally {
        	closeDB(conn);
        	closeDB(stmt, rs);
        }
	}

	/**
	 * checks if user has already submitted feedback
	 * 
	 * @return true if user has already submitted Feedback for lecture
	 */
	@Override
	public boolean checkRatingStatus(Lecture lecture) {

		// Get user that is currently logged in
		User usr = getUserFromSession();

		boolean result = false; // default value = false, is not changed unless
								// the user has already submitted feedback
		int lecid = lecture.getNumber(); // get lecture id
		int uid = usr.getID(); // get id of the user logged in

		Connection conn = null;
		PreparedStatement stmt = null;
		ResultSet rs = null;
		
		try {
			conn = DriverManager.getConnection(connStr);
			stmt = conn
					.prepareStatement("SELECT studentid FROM feedbackreceived WHERE lectureid=?");

			stmt.setInt(1, lecid);

			rs = stmt.executeQuery();
			int studentidTemp;

			while (rs.next()) {
				studentidTemp = rs.getInt("studentid");
				if (studentidTemp == uid) { // user's id was found
					result = true; // this means the user has already submitted
									// his feedback
				}
			}
		} catch (SQLException sqle)
        {
            printSQLException(sqle);
        } finally {
        	closeDB(conn);
        	closeDB(stmt, rs);
        }

		return result;
	}
	
	/**
	 * returns all relevant lectures for the user that is currently logged in
	 * 
	 * @return a list of the lectures
	 */
	@Override
	public ArrayList<Lecture> requestLectures() {
		ArrayList<Lecture> result = new ArrayList<Lecture>();

		// Get user that is currently logged in
		User usr = getUserFromSession();

		int uid = usr.getID();

		Connection conn = null;
		PreparedStatement stmt = null;
		ResultSet rs = null;

		try {
			conn = DriverManager.getConnection(connStr);
			stmt = conn
					.prepareStatement("SELECT courses.COURSEID, courses.TITLE as COURSETITLE, courses.CODE, lectures.LECTUREID, lectures.TITLE as LECTURETITLE, lectures.LECDATE FROM courses, lectures, participants WHERE courses.COURSEID = participants.COURSEID AND STUDENTID = ? AND lectures.PARENTID = courses.COURSEID");
			stmt.setInt(1, uid);
			
			rs = stmt.executeQuery();
			
			int courseid, lecid;
			String coursetitle, coursecode, lectitle, lecdate;
			
			while (rs.next()) { // create lecture objects for each result row
								// and add them to the list
				courseid = rs.getInt("courseid");
				coursetitle = rs.getString("coursetitle");
				coursecode = rs.getString("code");
				lecid = rs.getInt("lectureid");
				lectitle = rs.getString("lecturetitle");
				lecdate = rs.getDate("lecdate").toString();
				Lecture tmp = new Lecture(lecid, lectitle, lecdate, courseid,
						coursetitle, coursecode);
				result.add(tmp);
			}
		} catch (SQLException sqle)
        {
            printSQLException(sqle);
        } finally {
        	closeDB(conn);
        	closeDB(stmt, rs);
        }

		return result;
	}

	/**
	 * returns all submitted feedback for a given lecture.
	 * 
	 * @param lecture
	 *            The lecture which is being rated.
	 */
	@Override
	public ArrayList<Feedback> requestFeedback(Lecture lecture) {
		ArrayList<Feedback> result = new ArrayList<Feedback>();
		// load appropriate JDBC driver
		Connection conn = null;
		PreparedStatement stmt = null;
		ResultSet rs = null;

		try {
			conn = DriverManager.getConnection(connStr);
			stmt = conn
					.prepareStatement("SELECT satisfaction, commentlike, commentdislike FROM feedback WHERE lectureid=?");
			int lecid = lecture.getNumber();
			stmt.setInt(1, lecid);
			
			rs = stmt.executeQuery();
			
			int sat;
			String like, dislike;
			
			while (rs.next()) {
				sat = rs.getInt("satisfaction");
				like = rs.getString("commentlike");
				dislike = rs.getString("commentdislike");
				Feedback tmp = new Feedback(sat, like, dislike, lecid);
				result.add(tmp);
			}
		} catch (SQLException sqle)
        {
            printSQLException(sqle);
        } finally {
        	closeDB(conn);
        	closeDB(stmt, rs);
        }
		return result;
	}

	/**
	 * returns a lecture object for a given id
	 * 
	 * @param the
	 *            lecture id
	 * @return a lecture object
	 */
	public Lecture getLecture(int id) {
		Lecture result = null;

		Connection conn = null;
		PreparedStatement stmt = null;
		ResultSet rs = null;
		
		try {
			conn = DriverManager.getConnection(connStr);
			
			stmt = conn
					.prepareStatement("SELECT courses.COURSEID, courses.TITLE as COURSETITLE, courses.CODE, lectures.LECTUREID, lectures.TITLE as LECTURETITLE, lectures.LECDATE FROM courses, lectures WHERE lectures.PARENTID = courses.COURSEID AND lectures.LECTUREID = ?");
			stmt.setInt(1, id);
			
			rs = stmt.executeQuery();
			
			int courseid, lecid;
			String coursetitle, coursecode, lectitle, lecdate;

			while (rs.next()) {
				courseid = rs.getInt("courseid");
				coursetitle = rs.getString("coursetitle");
				coursecode = rs.getString("code");
				lecid = rs.getInt("lectureid");
				lectitle = rs.getString("lecturetitle");
				lecdate = rs.getDate("lecdate").toString();
				
				result = new Lecture(lecid, lectitle, lecdate, courseid,
						coursetitle, coursecode);
			}

		} catch (SQLException sqle)
        {
            printSQLException(sqle);
        } finally {
        	closeDB(conn);
        	closeDB(stmt, rs);
        }

		return result;
	}

	
	/**
     * Prints details of an SQLException.
     *
     * @param e the SQLException in question.
     */
    private static void printSQLException(SQLException e)
    {
        /* go through all thrown exceptions in order to eventually
    	reach the root cause of the problem*/
        while (e != null)
        {
            System.err.println("\n----- SQL Exception -----");
            System.err.println("  SQL State:  " + e.getSQLState());
            System.err.println("  Error Code: " + e.getErrorCode());
            System.err.println("  Message:    " + e.getMessage());
            e = e.getNextException();
        }
    }
    
    /**
     * Closes the database connection, thus easing server load
     *
     * @param conn The database connection the method is supposed to close
     */
    private static void closeDB(Connection conn)
    {
    	//close connection
    	 try {
             if (conn != null) {
                 conn.close();
                 conn = null;
             }
         } catch (SQLException sqle) {
             printSQLException(sqle);
         }
    }
    
    /**
     * Closes the Statement and ResultSet, sets the references to null to
     * allow GC to free up memory
     *
     * @param stmt The Statement being used prior to the invocation of the closeDB method
     * @param rs The ResultSet containing the results of a finished query
     */
    private static void closeDB(Statement stmt, ResultSet rs)
    {
    	//close statement
    	try {
            if (stmt != null) {
                stmt.close();
                stmt = null;
            }
        } catch (SQLException sqle) {
            printSQLException(sqle);
        }
    	
    	//close result set
    	try {
            if (rs != null) {
                rs.close();
                rs = null;
            }
        } catch (SQLException sqle) {
            printSQLException(sqle);
        }
    	
    }

}